# 使用提醒:
# 1. xbot包提供软件自动化、数据表格、Excel、日志、AI等功能
# 2. package包提供访问当前应用数据的功能，如获取元素、访问全局变量、获取资源文件等功能
# 3. 当此模块作为流程独立运行时执行main函数
# 4. 可视化流程中可以通过"调用模块"的指令使用此模块
import xbot
from xbot import print, sleep
from . import package
from .package import variables as glv

import requests


def get_movie():
    url = 'http://www.boxofficecn.com/the-red-box-office'
    res = requests.get(url)
    print(res.text)


def main(args):
    pass


import re
import requests
from lxml import etree

# 缩写字母国家字典（🇨🇳_中国）
country_dict = {
    '🇨🇳': '中国',
    '🇺🇸': '美国',
    '🇯🇵': '日本',
    '🇭🇰': '香港',
    '🇹🇼': '台湾',
    '🇰🇷': '韩国',
    '🇩🇪': '德国',
    '🇫🇷': '法国',
    '🇮🇹': '意大利',
    '🇪🇸': '西班牙',
    '🇷🇺': '俄罗斯',
    "🇮🇳": '印度',
    "🇬🇧": '英国',
    "🇫🇮": '芬兰',
    "🇱🇧": '黎巴嫩共和国',
    "🇦🇺": '澳大利亚',
}


def get_movie():
    url = 'http://www.boxofficecn.com/the-red-box-office'
    res = requests.get(url)
    res_text = etree.HTML(res.text)
    tr_list = res_text.xpath('//*[@id="tablepress-4"]/tbody/tr')
    result = []
    for tr in tr_list:
        movie_time_and_country = tr.xpath('./td[1]/text()')[0]

        try:
            movie_time, movie_country_words = movie_time_and_country.split(' ')
        except Exception as e:
            print(e)
            # 正则识别年份与国家例如：2023🇨🇳,将年份2023与关键字段🇨🇳分别识别出来
            movie_time = re.findall(r'\d+', movie_time_and_country)[0]
            movie_country_words = movie_time_and_country.split(movie_time)[1]
            print(movie_time, movie_country_words)
        movie_country = country_dict[movie_country_words.strip()]
        movie_name_and_score = tr.xpath('./td[2]/text()')[0]
        movie_name = movie_name_and_score.split('（')[0]
        movie_score = movie_name_and_score.split('（')[1].split('）')[0]
        movie_actors = tr.xpath('./td[3]/text()')[0]
        movie_money_list = tr.xpath('./td[last()]/font/text()') or tr.xpath('./td[last()]/text()') or []
        if len(movie_money_list) == 0:
            movie_money = 0
        else:
            try:
                movie_money = int(movie_money_list[0])
            except:
                movie_money = int(movie_money_list[0][0:-1])
        # 将数据整合成json格式
        movie_dict = {
            'movie_time': movie_time,
            'movie_name': movie_name,
            'movie_score': movie_score,
            'movie_country': movie_country,
            'movie_actors': movie_actors,
            'movie_money': movie_money,
        }
        result.append(movie_dict)
    print(result)
    return result


def perfect_data(movie_data, submitter):
    mysql_insert_data = []
    for movie in movie_data:
        movies_tuple = (
            movie["movie_name"],
            movie["movie_time"],
            movie["movie_country"],
            movie["movie_score"],
            movie["movie_actors"],
            movie["movie_money"],
            submitter
        )
        mysql_insert_data.append(movies_tuple)
    print(mysql_insert_data)
    return mysql_insert_data


import pymysql


def insert_movie(movie_data):
    try:
        # 连接数据库
        conn = pymysql.connect(host='43.143.30.32',
                               user='yingdao',
                               password='9527',
                               database='ydtest',
                               charset='utf8')
        my_cursor = conn.cursor()

        # sql语句
        insert_sql = """
                     INSERT INTO movie (电影名称, 上映年份, 制片地区, 评分, 导演, 票房, 提交人)
                     VALUES (%s, %s, %s, %s, %s, %s, %s) \
                     """

        for movie in movie_data:
            try:
                my_cursor.execute(insert_sql, movie)
                conn.commit()
                print(f"{movie}数据插入成功")
            except Exception as e:
                print(e)
                conn.rollback()
                print(f"{movie}数据插入失败")
    except Exception as e:
        print(e)

    finally:
        if my_cursor is not None:
            my_cursor.close()
        if conn is not None:
            conn.close()


def main(submitter):
    movies_data = get_movie()
    movies_datas = perfect_data(movies_data, submitter)
    insert_movie(movies_datas)